package com.eam.handler.facilities;

import com.basis.handler.service.DataImportInterface;
import com.basis.utils.CodeGenerateRuleUtil;
import com.eam.data.EamSpareparts;
import com.eam.data.EamSparepartsCollar;
import com.mes.data.EmesDataImport;
import com.mes.data.EmesProcessOpr;
import com.mes.data.EmesVendor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Locator;
import org.openbravo.model.common.enterprise.Organization;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

//设备备品配件物料入库导入

@SuppressWarnings("unchecked")
public class SparepartsImportService extends DataImportInterface {
	protected static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式
	protected String tableName = null;
	protected String clientId = null;
	protected String orgId = null;
	protected String userId = null;
	private boolean flag = true;
	private String maintainid = "";
	private String itemid = "";
	private String batchs = CodeGenerateRuleUtil.getCode("Eam_Spareparts_Collar", "batch");   //获取批次条码
    
	public void setEmesDataImport(EmesDataImport dataImport) {
	}

	public JSONObject importData(InputStream is, String fileName) throws Exception {
		JSONObject result = new JSONObject();
		try {
			Workbook wb = null;
			if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			if (wb != null) {
				result = this.readExcel(wb);
				if (!result.getBoolean("result")) {
					OBDal.getInstance().rollbackAndClose();
					return result;
				} else {
					OBDal.getInstance().commitAndClose();
					result.put("result", true);
					result.put("msg", "导入成功");
					return result;
				}
			} else {
				result.put("msg", "文件格式错误，至于许EXCEL的导入");
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			if (e.getClass().getName().contains("ConstraintViolationException")) {
				result.put("result", false);
				result.put("msg", "文件数据有问题，请仔细检查。");
			}
		} finally {
			is.close();
		}
		return result;
	}
	
	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "", errorMsg;
		int number = wb.getNumberOfSheets();
		JSONObject result = new JSONObject();
		
		
		result.put("result", true);
		if (number <= 0) {
			result.put("result", false);
			result.put("msg", "文件格式错误。请仔细检查");
			return result;
		} else {
			// 读取第一页
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 2; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
			    
				errorMsg = this.checkData(row);
				if (errorMsg != null && !"".equals(errorMsg)) {
					meg = errorMsg;
				}
				
			}
			if (!"".equals(meg)) {
				result.put("result", false);
				result.put("msg", meg);
			}
			return result;
			
		}
	}
	
	
	/**
	 * 开始存储记录
	 * 
	 * @param row
	 * @return
	 * @throws Exception
	 */
	
	private String checkData(Row row) throws Exception {
		String meg = "";
		try {
			boolean jieguo = true;
			Object code = readValue(row.getCell(0)); //物料编码
			Object name = readValue(row.getCell(1)); //物料名称
			Object model = readValue(row.getCell(2)); //型号/规格
			Object Numbers = readValue(row.getCell(3));  //入库数量
			Object Safety_Stock = readValue(row.getCell(4)); //安全库存数量
			Object Company = readValue(row.getCell(5)); //单位
			Object Price= readValue(row.getCell(6)); //单价
			Object Vendor = readValue(row.getCell(7));//供应商
			Object org = readValue(row.getCell(8));//车间
			Object Processop = readValue(row.getCell(9)); //厂房区域
			Object Locators = readValue(row.getCell(10)); //储位
			Object Cycle = readValue(row.getCell(11)); //备件类型/周期
			Object Sparefield = readValue(row.getCell(12)); //使用情况
			String type = "added";  //代表入库类型
			
			String userIdtime = SessionInfo.getUserId(); //登录人的ad_user_id
			Date time  =sdf.parse(sdf.format(new Date())); //获取当前系统时间
			
			String eql = "select * from ad_user where ad_user_id = ?";
			
			User userIds = (User)OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(User.ENTITY_NAME).setString(0, userIdtime).uniqueResult();
			
			//判断入库的物料是否已经在物料清单里面了
			String sql = "select * from Eam_Spareparts wl where wl.code = ? ";
			
			EamSpareparts results = (EamSpareparts)OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(EamSpareparts.ENTITY_NAME).setString(0, code.toString()).uniqueResult();
			
			//查询厂房区域的ID
			String aql = " select * from emes_processopr where name = "+"'"+Processop.toString()+"'"+" or (not name = "+"'"+Processop.toString()+"'"+" and code = "+"'"+Processop.toString()+"'"+" )";
			
			EmesProcessOpr gongxu = (EmesProcessOpr)OBDal.getInstance().getSession().createSQLQuery(aql).addEntity(EmesProcessOpr.ENTITY_NAME).uniqueResult();
			
			//查询物料入库的储位
			String rql = "select * from m_locator where barcode = ? and m_warehouse_id= 'FADC875A87544C9D80168957068700D1' ";
			
			Locator  kuwei = (Locator)OBDal.getInstance().getSession().createSQLQuery(rql).addEntity(Locator.ENTITY_NAME).setString(0,Locators.toString()).uniqueResult();
			
			//查询物料入库的供应商
			String tql = "select * from emes_vendor where name = ? ";
			
			EmesVendor vendor = (EmesVendor)OBDal.getInstance().getSession().createSQLQuery(tql).addEntity(EmesVendor.ENTITY_NAME).setString(0,Vendor.toString()).uniqueResult();
			
			//获取选择的车间
			String cql = "select * from ad_org where name = ? ";
			
			Organization adorg = (Organization)OBDal.getInstance().getSession().createSQLQuery(cql).addEntity(Organization.ENTITY_NAME).setString(0, org.toString()).uniqueResult();
			
			
			
			BigDecimal anquannumbers = new BigDecimal(Safety_Stock.toString());
			
			BigDecimal Number = new BigDecimal(Numbers.toString());
			
			BigDecimal Safety_Stocks = new BigDecimal(Safety_Stock.toString());
			
			
			
			
			//备品配件物料清单
			EamSpareparts materiel = new EamSpareparts();
			
			//备品配件入库记录
			EamSparepartsCollar materielIn = new EamSparepartsCollar();
			
			//EamSpareparts materiels = OBDal.getInstance().get(EamSpareparts.class, results);
			String materielId = "";
			
			String companys = "";
			
			if(Company.equals("对")){
				companys= "10";
			}else if(Company.equals("个")){
				companys= "20";
			}else if(Company.equals("根")){
				companys= "30";
			}else if(Company.equals("盒")){
				companys= "40";
			}else if(Company.equals("卷")){
				companys= "50";
			}else if(Company.equals("块")){
				companys= "60";
			}else if(Company.equals("片")){
				companys= "70";
			}else if(Company.equals("套")){
				companys= "80";
			}else{
				jieguo = false;
			}
			
			
			//if(adorg == null || adorg.equals("")){
				//meg = "【第" + (row.getRowNum() + 1) + "行,车间不存在！！";
			//}else{
				//if(gongxu == null || gongxu.equals("")){
					//meg = "【第" + (row.getRowNum() + 1) + "行,工序不存在！！";
				//}else{
					if(kuwei == null || kuwei.equals("")){
						meg = "【第" + (row.getRowNum() + 1) + "行,储位不存在！！";
					}else{
						//if(vendor == null || vendor.equals("")){
							//meg = "【第" + (row.getRowNum() + 1) + "行,供应商不存在！！";
						//}else{
							if(!jieguo){
								meg = "【第" + (row.getRowNum() + 1) + "行,单位不存在！！";
							}else{
//								if(Price.equals("")|| Price == null){
//									meg = "【第" + (row.getRowNum() + 1) + "行,请输入单价！！！";
//								}else{
									
									if(results == null){
										materiel.setOrganization(adorg);
										materiel.setCode(code.toString());
										materiel.setName(name.toString());
										materiel.setModel(model.toString());
										materiel.setSafetyStock(anquannumbers);
										materiel.setCompany(companys);
										materiel.setEmesProcessopr(gongxu);
										materiel.setCycle(Cycle.toString());
										materiel.setSparefield(Sparefield.toString());
										OBDal.getInstance().save(materiel);
										materielId = materiel.getId();
										OBDal.getInstance().flush();
										
										String wql = "select * from Eam_Spareparts where Eam_Spareparts_id = ? ";
										
										EamSpareparts wl = (EamSpareparts)OBDal.getInstance().getSession().createSQLQuery(wql).addEntity(EamSpareparts.ENTITY_NAME).setString(0, materielId).uniqueResult();
										
										materielIn.setOrganization(adorg);
										materielIn.setEamSpareparts(wl);
										materielIn.setLocator(kuwei);
										materielIn.setCompany(companys);
										materielIn.setNumbers(Number);
										materielIn.setStockNumbers(Safety_Stocks);
										if(vendor == null || vendor.equals("")){
											
										}else{
											BigDecimal Prices = new BigDecimal(Price.toString());
											
											materielIn.setPrice(Prices);
										}
										materielIn.setEmesProcessopr(gongxu);
										materielIn.setStorageTime(time);
										materielIn.setBatch(batchs);
										materielIn.setUser(userIds);
										materielIn.setType(type);
										materielIn.setEmesVendor(vendor);
										OBDal.getInstance().save(materielIn);
										
									}else{
										
										EamSpareparts wl = (EamSpareparts)OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(EamSpareparts.ENTITY_NAME).setString(0, code.toString()).uniqueResult();
										List<EamSparepartsCollar> scList = wl.getEamSparepartsCollarList();
										if(scList.size() > 0) {
											EamSparepartsCollar esc = scList.get(0);
											if(Number != null && esc.getNumbers() != null) {
												Number.add(esc.getNumbers());
											}
										}
										materielIn.setOrganization(adorg);
										materielIn.setEamSpareparts(wl);
										materielIn.setLocator(kuwei);
										materielIn.setCompany(companys);
										materielIn.setNumbers(Number);
										materielIn.setStockNumbers(Safety_Stocks);
										if(vendor == null || vendor.equals("")){
											
										}else{
											BigDecimal Prices = new BigDecimal(Price.toString());
											
											materielIn.setPrice(Prices);
										}
										materielIn.setEmesProcessopr(gongxu);
										materielIn.setStorageTime(time);
										materielIn.setBatch(batchs);
										materielIn.setUser(userIds);
										materielIn.setType(type);
										materielIn.setEmesVendor(vendor);
										OBDal.getInstance().save(materielIn);
										
									}
								//}
								
							}
						//}
					}
				//}
			//}
			
						
		} catch (Exception e) {
			e.printStackTrace();
		}
		return meg;
		
	}
	
	/**
	 * 读取单元格数据
	 */
	protected Object readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Double cellValue = null;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			cellValue = cell.getNumericCellValue();
			return cellValue.longValue() < cellValue ? cellValue : cellValue.longValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new RuntimeException("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}